package cn.com.nes.site.module.dao.crm;


import java.math.BigDecimal;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import cn.com.nes.common.util.Page;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.com.nes.site.module.dao.UserDaoImpl;


@Repository
public class AnalysisOppDaoImpl extends BaseDaoImpl {
	private TransactionStatus newg_status = null;

	@Resource
	private UserDaoImpl userDaoImpl;
	
	//商机总体分析  total  newopp  deal shut
	public PageData getTotalOpportunity(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			
			//商机目列表
			sb.delete(0, sb.length()) ;
			sb.append("SELECT count(*) total,if(sum(IF (opportunitystage='01',1,	0)) is null ,0,sum(IF (opportunitystage='01',1,	0))) newopp,	if (sum(IF (opportunitystage = '06',	1,0	)) is null,0,sum(IF (opportunitystage = '06',	1,0	))) deal,if(sum(IF (opportunitystage = '07',1,	0)) is null,0,sum(IF (opportunitystage = '07',1,	0))) shut,if(sum(IF (c.uuid is not null ,1,	0)) is null,0,sum(IF (c.uuid is not null ,1,	0))) design  " +
					" FROM opportunity a	") ;
			sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
			sb.append(" left join opportunity_bim c on a.uuid=c.opportunityId  ") ;
			sb.append("where a.isdelete='0' and b.companyId like concat(?,'%') ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("TotalOpportunity", list) ;
			
			//三个月内商机
			sb.delete(0, sb.length()) ;
			sb.append("SELECT count(*) total,if(sum(IF (a.createTime > date_sub(now(), INTERVAL 3 MONTH),1,	0)) is null,0,sum(IF (a.createTime > date_sub(now(), INTERVAL 3 MONTH),1,	0))) newopp, if(sum(IF (a.modifyTime > date_sub(now(), INTERVAL 3 MONTH) AND a.opportunitystage = '06',	1,0	)) is null,0,sum(IF (a.modifyTime > date_sub(now(), INTERVAL 3 MONTH) AND a.opportunitystage = '06',	1,0	))) deal,if(sum(IF (a.modifyTime > date_sub(now(), INTERVAL 3 MONTH)	AND a.opportunitystage = '07',1,0)) is null,0,sum(IF (a.modifyTime > date_sub(now(), INTERVAL 3 MONTH)	AND a.opportunitystage = '07',1,	0))) shut ,if(sum(IF (c.modifyTime > date_sub(now(), INTERVAL 3 MONTH)	,1,	0)) is null,0,sum(IF (c.modifyTime > date_sub(now(), INTERVAL 3 MONTH)	,1,	0))) design " +
					"FROM opportunity a ") ;
			sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
			sb.append(" left join opportunity_bim c on a.uuid=c.opportunityId  ") ;
			sb.append(" where a.isdelete='0' and b.companyId  like concat(?,'%') ");
			 list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("NearlyThreeMonthOpportunity", list) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}
	//商机各阶段分析
		public PageData getOpportunityStagePercentage(DataReq req) {
			PageData res = new PageData();
			StringBuffer sb = new StringBuffer();
		try {
				
				//线索目列表
				sb.delete(0, sb.length()) ;
				sb.append("SELECT opportunitystage,c.name opportunitystagename,count(*) cnt	") ;
				
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append(" left JOIN t_param c on a.opportunitystage=c.`value` and c.param_type='16'  ") ;
				 sb.append("where a.isdelete='0' and b.companyId like concat(?,'%') ");
			    sb.append("   group by opportunitystage ") ;
				List<Map<String, Object>> list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
				
				BigDecimal sumopp = new BigDecimal("0");//总商机
				
				for(int i=0;i<list.size();i++){
					BigDecimal cnt1 = new BigDecimal((Long) list.get(i).get("cnt"));
					sumopp = sumopp .add(cnt1);
			     }
				for(int j=0;j<list.size();j++){
					BigDecimal cnt2 = new BigDecimal((Long) list.get(j).get("cnt"));
					
					BigDecimal per = cnt2.multiply(
							new BigDecimal("100")).divide(sumopp, 2,
									BigDecimal.ROUND_HALF_EVEN);
							
							
					list.get(j).put("percentage", per.toString());
		
			     }
   			    
   				res.setPage(list);	
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}
			return res;
		}
	
		
		//商机转化率
		public PageData getOpportunityConversionRate(DataReq req) {
			Map<String, Object> param = req.getReqData();
			PageData res = new PageData();
			StringBuffer sb = new StringBuffer();
			Map<String,Object> extData = new LinkedHashMap<String, Object>();
			
			String ConversionRate="";//商机转化率
			String ExpectedMoney="";
			String ExpectedCount="";
		try {
				
			//商机转化率
				sb.delete(0, sb.length()) ;
				sb.append("SELECT if(round(sum(if(opportunitystage='06',1,0))/count(*)*100,2) is null ,0,round(sum(if(opportunitystage='06',1,0))/count(*)*100,2)) ConversionRate	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				 sb.append("where a.isdelete='0' and b.companyId  like concat(?,'%') ");

				List<Map<String, Object>> list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
				if(list.size()>0){
					ConversionRate =  list.get(0).get("ConversionRate").toString() ;//总商机
				}
				
				//预期成单金额
				sb.delete(0, sb.length()) ;
				sb.append("SELECT if(sum(opportunitymoney) is null,0,sum(opportunitymoney)) ExpectedMoney,count(*) ExpectedCount	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				 sb.append("where a.isdelete='0' and estimatedclosingdate>=now() and estimatedclosingdate<=date_add(now(),interval 3 month) and b.companyId like concat(?,'%') ");
			 	list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
				if(list.size()>0){
					ExpectedMoney =  list.get(0).get("ExpectedMoney").toString() ;
					ExpectedCount =  list.get(0).get("ExpectedCount").toString() ;
				}
		
				param.put("ConversionRate", ConversionRate) ;
				param.put("ExpectedMoney", ExpectedMoney) ;
				param.put("ExpectedCount", ExpectedCount) ;
				
				extData.put("ConversionRate", param) ;
				
				res.setExtData(extData);		
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}
			return res;
		}
		
		//商机地图展示
		public PageData getOpportunityMap(DataReq req) {
			Map<String, Object> param = req.getReqData();
			PageData res = new PageData();
			StringBuffer sb = new StringBuffer();
			Map<String,Object> extData = new LinkedHashMap<String, Object>();
			
		try {
				//商机地图展示
				sb.delete(0, sb.length()) ;
				sb.append("SELECT a.uuid,opportunitystage,c.name opportunitystagenme,opportunitytype,d.name opportunitytypename,opportunityname,a.customerId,e.customername,a.xzqhdm,f.xzqhqc,projectaddress,projectarea,a.longitude,a.latitude,opportunitymoney,date_format(a.estimatedclosingdate,'%Y-%m-%d %T') estimatedclosingdate	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append(" left JOIN t_param c on a.opportunitystage=c.`value` and c.param_type='16'  ") ;
				sb.append(" left JOIN t_param d on a.opportunitytype=d.`value` and d.param_type='17'  ") ;
				sb.append(" left JOIN customer e on a.customerId=e.`uuid`  ") ;
				sb.append(" LEFT JOIN t_xzqh f on a.xzqhdm=f.`xzqhdm`  ") ;
				 sb.append("where a.isdelete='0' and a.longitude is not null  and a.latitude is not null and b.companyId like concat(?,'%') ");
			  
			    List<Map<String, Object>> list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
			    Iterator<Map<String, Object>> iterator = list.iterator();
			    
			    while(iterator.hasNext()){
			    	BigDecimal[] lnglat = new BigDecimal[2];
			    	Map<String, Object> next = iterator.next();
			    	BigDecimal lng = (BigDecimal) next.get("longitude");
			    	BigDecimal lat = (BigDecimal) next.get("latitude");
			    	lnglat[0] = lng;
			    	lnglat[1] = lat;
			    	next.put("lnglat", lnglat);
			    }
			    extData.put("OpportunityMap", list) ;
			    
			    
			  //各地商机排行
				sb.delete(0, sb.length()) ;
				sb.append("SELECT SUBSTR(a.xzqhdm ,1,9),f.xzqhmc xzqhmc,count(*) cnt ") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append(" inner JOIN t_xzqh f on CONCAT(SUBSTR(a.xzqhdm ,1,9),'000')=f.`xzqhdm`  ") ;
				 sb.append("where a.isdelete='0' and a.longitude is not null and a.latitude is not null and b.companyId like concat(?,'%') ");
			    sb.append("   group by SUBSTR(a.xzqhdm ,1,9) order by cnt desc ") ;
			     list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
			    extData.put("OpportunityAreaSort", list) ;
				
				res.setExtData(extData);		
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}
			return res;
		}
		
		//商机占比
		public PageData getOpportunityPer(DataReq req) {
		    PageData res = new PageData();
			StringBuffer sb = new StringBuffer();
			Map<String,Object> extData = new LinkedHashMap<String, Object>();
			
		try {
				//商机类型占比
				sb.delete(0, sb.length()) ;
				sb.append(" select t1.opportunitytypename,t1.typecnt , round(t1.typecnt/t2.totalcnt*100,2) typeper  from  ") ;
				sb.append("(SELECT opportunitytype,d.name opportunitytypename,count(*) typecnt	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append(" left JOIN t_param d on a.opportunitytype=d.`value` and d.param_type='17'  ") ;
				sb.append("where a.isdelete='0'  and b.companyId like concat(?,'%') ");
				sb.append("   group by a.opportunitytype ) t1 ,") ;
				 
				sb.append(" (SELECT count(*) totalcnt	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
					sb.append("where a.isdelete='0'  and b.companyId like concat(?,'%') ");
				sb.append(" ) t2 ") ;
					 
			    List<Map<String, Object>> list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId(),req.getUinfo().getCompanyId() });
			    extData.put("opportunitytype", list) ;
			    
			    
			  //商机转换时间
				sb.delete(0, sb.length()) ;
				sb.append("SELECT  if(TIMESTAMPDIFF(MONTH,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(a.modifyTime,'%Y-%m-%d %H:%i:%S'))=0," +
						"'一个月内',if(TIMESTAMPDIFF(MONTH,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(a.modifyTime,'%Y-%m-%d %H:%i:%S'))<3 and " +
						"TIMESTAMPDIFF(MONTH,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(a.modifyTime,'%Y-%m-%d %H:%i:%S'))>0 ," +
						"'三个月',if(TIMESTAMPDIFF(MONTH,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(a.modifyTime,'%Y-%m-%d %H:%i:%S'))<6 " +
						"and TIMESTAMPDIFF(MONTH,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(a.modifyTime,'%Y-%m-%d %H:%i:%S'))>=3,'半年','半年以上') )) tj,count(*) cnt ") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				 sb.append("where a.isdelete='0' and a.opportunitystage='06'  and b.companyId like concat(?,'%') ");
			    sb.append("   group by tj") ;
			     list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
			    extData.put("OpportunityCloseDate", list) ;
			    
			    
			    //客户性质
			    sb.delete(0, sb.length()) ;
				sb.append(" select t1.customernaturename,t1.typecnt , round(t1.typecnt/t2.totalcnt*100,2) typeper  from  ") ;
				sb.append("(SELECT e.customernature,d.name customernaturename,count(*) typecnt	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append(" left JOIN customer e on a.customerId=e.`uuid`  ") ;
				sb.append(" left JOIN t_param d on e.customernature=d.`value` and d.param_type='20'  ") ;
				sb.append("where a.isdelete='0'  and b.companyId like concat(?,'%') ");
				sb.append("   group by e.customernature ) t1 ,") ;
				 
				sb.append(" (SELECT count(*) totalcnt	") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append("where a.isdelete='0'  and b.companyId like concat(?,'%') ");
				sb.append(" ) t2 ") ;
					 
			     list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId(),req.getUinfo().getCompanyId() });
			    extData.put("customernature", list) ;
				
				res.setExtData(extData);		
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}
			return res;
		}
		
		//商机预期成交金额
		public PageData getOpportunitymoney(DataReq req) {
			Map<String, Object> param = req.getReqData();
			PageData res = new PageData();
			StringBuffer sb = new StringBuffer();
			Map<String,Object> extData = new LinkedHashMap<String, Object>();
			
		try {
				
			  //商机转换时间
				sb.delete(0, sb.length()) ;
				sb.append("SELECT  sum(Opportunitymoney) Opportunitymoney,date_format(a.estimatedclosingdate,'%Y-%m') estimatedclosingdate ") ;
				sb.append(" from opportunity  a ") ;
				sb.append(" left join t_user b on a.opportunityowner=b.id  ") ;
				sb.append("where a.isdelete='0' and (a.opportunitystage!='06' or a.opportunitystage!='07'  )   and b.companyId like concat(?,'%') ");
			    sb.append("   group by date_format(a.estimatedclosingdate,'%Y-%m')") ;
			    List<Map<String, Object>> list = this.getJdbcTemplate_newg()
						.queryForList(sb.toString(),
								new Object[] {req.getUinfo().getCompanyId() });
			    extData.put("Opportunitymoney", list) ;
			    
				res.setExtData(extData);		
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}
			return res;
		}
		
		//商机员工跟进
		public PageData getOpportunityStaffCheck(DataReq req) {
			Map<String, Object> param = req.getReqData();
			PageData res = new PageData();
			StringBuffer sb = new StringBuffer();
			Map<String,Object> extData = new LinkedHashMap<String, Object>();
			
		try {
			String cluebelongtocompany = req.getUinfo().getCompanyId();	
			  //商机转换时间
			sb.delete(0, sb.length()) ;
  			sb.append(" SELECT	u.id userId,	u.nickName,	d.departmentName, ") ;
  			sb.append(" (select count(*) from opportunity m where m.opportunityowner=u.id and m.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)) totalcnt, ") ;
  			sb.append(" (select count(*) from opportunity n where n.opportunityowner=u.id and n.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH) and n.opportunitystage = '06') ChangeToProject, ") ;
  			sb.append(" 0 Contract, ") ;
  			sb.append(" (select count(*) from log_operate a where a.userid=u.id  and a.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH) and a.type='03') OperateCount, ") ;
  			sb.append(" (select count(*) from log_claim b where b.createUid=u.id and b.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH) and b.type='03') ClaimCount ") ;
  			sb.append(" FROM	t_user u ") ;
  			sb.append(" LEFT JOIN t_department d ON d.departmentId = u.departmentId ") ;
  			sb.append(" WHERE u.sfsc=0 and	u.companyId LIKE concat(?, '%') ") ;
  			sb.append(" GROUP BY	u.id ") ;
  				      
			    Page page = new Page(this.getJdbcTemplate_newg(), " from (" + sb.toString() + ") t", req.getPageid(), req.getPagesize(), res, new Object[]{ cluebelongtocompany }) ;
	            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
	            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{ cluebelongtocompany });
	            extData.put("OpportunityStaffCheck", list) ;
			    
				res.setExtData(extData);		
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
			} finally {

			}
			return res;
		}
}